package com.probiz.estore.sales.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.probiz.estore.sales.dao.GiftDao;
import com.probiz.estore.sales.model.GiftSearchCriteria;
import com.probiz.estore.common.model.sales.Gift;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.core.search.SearchCriteria;

/**
 * Dao implementation for Gift.
*/
public class GiftDaoImpl extends HibernateGenericDaoImpl<Gift> implements GiftDao {

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.sales.dao.GiftDao#countGiftByCategoryId(java.lang.Integer)
	 */
	public Integer countGiftByCategoryId(Integer categoryId) {
		String hql = "select count(g.giftId) from Gift g where g.category.categoryId=? and g.status>=0 ";
		Long itemCount = (Long) this.findUnique(hql, categoryId);
		return new Integer(itemCount.toString());
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.sales.dao.GiftDao#searchByCriteria(com.probiz.estore.sales.model.GiftSearchCriteria,
	 *      com.probiz.estore.core.search.SearchCriteria)
	 */
	public List<Gift> searchByCriteria(GiftSearchCriteria giftSearchCriteria,
			SearchCriteria searchCriteria) {
		StringBuffer hqlsb = new StringBuffer(100);
		List<Object> paramList = new ArrayList<Object>();
		// start:构建HQL语句
		hqlsb.append("from Gift g where 1=1 ");

		// -start:礼品编码
		if (StringUtils.isNotBlank(giftSearchCriteria.getGiftCode())) {
			hqlsb.append(" and g.giftCode like ?");
			paramList.add("%" + giftSearchCriteria.getGiftCode() + "%");
		}
		// -end:礼品编码

		// -start:礼品名称
		if (StringUtils.isNotBlank(giftSearchCriteria.getGiftName())) {
			hqlsb.append(" and g.giftName like ?");
			paramList.add("%" + giftSearchCriteria.getGiftName() + "%");
		}
		// -end:礼品名称

		// -start:最低价
		if (null != giftSearchCriteria.getLowestPrice()) {
			hqlsb.append(" and g.price >= ?");
			paramList.add(giftSearchCriteria.getLowestPrice());
		}
		// -end:最低价
		// -start:最高价
		if (null != giftSearchCriteria.getHighestPrice()) {
			hqlsb.append(" and g.price <= ?");
			paramList.add(giftSearchCriteria.getHighestPrice());
		}
		// -end:最高价

		// -start:最低积分
		if (null != giftSearchCriteria.getLowestPoint()) {
			hqlsb.append(" and g.point >= ?");
			paramList.add(giftSearchCriteria.getLowestPoint());
		}
		// -end:最积分
		// -start:最高积分
		if (null != giftSearchCriteria.getHighestPoint()) {
			hqlsb.append(" and g.point <= ?");
			paramList.add(giftSearchCriteria.getHighestPoint());
		}
		// -end:最高积分

		// -begin:礼品目录
		if (!ArrayUtils.isEmpty(giftSearchCriteria.getCategoryPaths())) {
			hqlsb.append(" and (");
			for (int i = 0; i < giftSearchCriteria.getCategoryPaths().length; i++) {
				hqlsb.append("g.category.categoryPath like ?");
				if (i < giftSearchCriteria.getCategoryPaths().length - 1)
					hqlsb.append(" or ");
				paramList.add(giftSearchCriteria.getCategoryPaths()[i] + "%");
			}
			hqlsb.append(" )");
		}
		if (StringUtils.isNotBlank(giftSearchCriteria.getCategoryPath())) {
			hqlsb.append(" and (");
			hqlsb.append("g.category.categoryPath like ?");
			hqlsb.append(" )");
			paramList.add(giftSearchCriteria.getCategoryPath() + "%");

		}
		if (StringUtils.isNotBlank(giftSearchCriteria.getCategoryId())) {
			hqlsb.append(" and (");
			hqlsb.append("g.category.categoryId = ?");
			hqlsb.append(" )");
			paramList.add(new Integer(giftSearchCriteria.getCategoryId()));
		}
		// -end:礼品目录

		// -start:状态
		if (null != giftSearchCriteria.getStatus()) {
			hqlsb.append(" and g.status = ?");
			paramList.add(giftSearchCriteria.getStatus());
		}
		// -end:状态
		
		// start:order by
		if (null != giftSearchCriteria.getOrderby()) {
			hqlsb.append(" order by ").append(
					getSortByString(giftSearchCriteria.getOrderby()));
		} else {
			hqlsb.append(" order by g.giftId desc");
		}
		// end:order by
		// end:构建HQL语句
		searchCriteria.setHql(hqlsb.toString());
		searchCriteria.removeParamValues();
		for (Object object : paramList) {
			searchCriteria.addParamValue(object);
		}
		List<Gift> giftlist = searchByCriteria(searchCriteria);
		return giftlist;
	}

	/**
	 * 根据orderby获得 hql语句
	 * 
	 * @param orderby
	 *            排序id
	 * @return hql语句
	 */
	private String getSortByString(short orderby) {
		String orderByString;
		switch (orderby) {
			case 0: // 前台默认
				orderByString = "g.sortOrder asc,g.createTime desc";
				break;
			case 1:
				orderByString = "g.price asc";
				break;
			case 2:
				orderByString = "g.price desc";
				break;
			case 3:
				orderByString = "convert_gbk(g.giftName) asc";
				break;
			case 4:
				orderByString = "convert_gbk(g.giftName) desc";
				break;
			case 5:
				orderByString = "g.createTime asc";
				break;
			case 6:
				orderByString = "g.createTime desc";
				break;
			case 7:
				orderByString = "g.updateTime asc";
				break;
			case 8:
				orderByString = "g.updateTime desc";
				break;
			case 9:
				orderByString = "g.point asc";
				break;
			case 10:
				orderByString = "g.point desc";
				break;
			case 11:
				orderByString = "g.status asc";
				break;
			case 12:
				orderByString = "g.status desc";
				break;
			case 13:
				orderByString = "g.giftCode asc";
				break;
			case 14:
				orderByString = "g.giftCode desc";
				break;
			case 15:
				orderByString = "g.quantityOnHand asc";
				break;
			case 16:
				orderByString = "g.quantityOnHand desc";
				break;
			case 17:
				orderByString = "convert_gbk(g.category.categoryName) asc";
				break;
			case 18:
				orderByString = "convert_gbk(g.category.categoryName) desc";
				break;
			default:
				orderByString = "g.giftId desc";
				break;
		}
		return orderByString;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.probiz.estore.sales.dao.GiftDao#getSizeByGiftCodeByNotGiftId(java.lang.String,
	 *      java.lang.Integer)
	 */
	public Long getSizeByGiftCodeByNotGiftId(final String giftCode,
			final Integer giftId) {
		Long size;
		if (giftId != null) {

			size = (Long) getHibernateTemplate().executeWithNewSession(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							StringBuffer sb = new StringBuffer();
							sb
									.append("select count(*) from Gift g where g.giftCode = ? and g.giftId != ?");
							Query query = session.createQuery(sb.toString());
							query.setString(0, giftCode);
							query.setInteger(1, giftId);
							Long rows = (Long) query.uniqueResult();
							return rows;
						}

					});
		} else {
			size = (Long) getHibernateTemplate().executeWithNewSession(
					new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							StringBuffer sb = new StringBuffer();
							sb
									.append("select count(*) from Gift g where g.giftCode = ? ");
							Query query = session.createQuery(sb.toString());
							query.setString(0, giftCode);
							Long rows = (Long) query.uniqueResult();
							return rows;
						}

					});
		}
		return size;

	}
	
	public Gift getGiftById(Integer giftId){
		return (Gift) this.findUnique("from Gift g where g.giftId=?", giftId);
	}
}
